﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

/// <summary>
/// Summary description for ManagementCustomer
/// </summary>
public class ManagementCustomer
{
    SqlConnection coon;
	public ManagementCustomer()
	{
        coon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
	}
    /*********** Customer ***********/

    public DataSet GetCustomer()
    {
        if (coon.State == ConnectionState.Closed)
        {
            coon.Open();
        }
        DataSet dt = new DataSet();
        string sql = "select * from Customer order by CustomerID DESC";
        SqlDataAdapter sda = new SqlDataAdapter(sql, coon);
        sda.Fill(dt);
        return dt;
    }

    public string GetLastCustomerID()
    {
        DataSet dt = new DataSet();
        string sql = "select * from Customer order by CustomerID DESC";
        SqlDataAdapter sda = new SqlDataAdapter(sql, coon);
        sda.Fill(dt);
        return dt.Tables[0].Rows[0].ItemArray[0].ToString();
    }

    public bool DeleteCustomer(string id)
    {
        if (coon.State == ConnectionState.Closed)
            coon.Open();
        string sql1 = "select * from [Contract] where CustomerID = '" + id + "'";
        SqlCommand cmd1 = new SqlCommand(sql1, coon);
        SqlDataReader sda = cmd1.ExecuteReader();
        if (sda.HasRows)
        {
            sda.Close();
            return false;
        }
        sda.Close();

        string sql = "DELETE FROM  Customer where CustomerID='" + id + "'";
        SqlCommand cmd = new SqlCommand(sql, coon);
        int i = cmd.ExecuteNonQuery();

        coon.Close();
        return i > 0 ? true : false;
    }

    public DataSet SearchCustomer(string s)
    {
        if (coon.State == ConnectionState.Closed)
            coon.Open();
        string sql = "select * from Customer where CustomerName like '%" + s + "%' order by CustomerID DESC";
        DataSet dt = new DataSet();
        SqlDataAdapter sda = new SqlDataAdapter(sql, coon);
        sda.Fill(dt);

        return dt;
    }


    public bool UpdateCustomer(string id, string name, string phone, string email, string address, string password)
    {
        if (coon.State == ConnectionState.Closed)
        { coon.Open(); }

        string sql = "update Customer set CustomerName='" + name + "',Phone='" + phone + "',Email='" + email + "',[Address]='" + address + "' ,[Password] = '" + password + "'  where CustomerID='" + id + " '";
        SqlCommand cmd = new SqlCommand(sql, coon);


        int i = cmd.ExecuteNonQuery();
        coon.Close();
        return i > 0 ? true : false;
    }

    public bool CheckCustomerUsername(string s)
    {
        if (coon.State == ConnectionState.Closed)
            coon.Open();
        string sql = "select * from Customer where CustomerID='" + s + "'";
        SqlCommand cmd = new SqlCommand(sql, coon);
        SqlDataReader sda = cmd.ExecuteReader();

        int i = 0;
        if (sda.HasRows)
        { i = 1; }
        sda.Close();
        return i == 1 ? true : false;
    }

    public ORANGE_PROJECTModel.Customer GetEditCustomer(string id)
    {
        if (coon.State == ConnectionState.Closed)
            coon.Open();
        DataSet dt = new DataSet();
        string sql = "select * from Customer where CustomerID='" + id + "' ";
        SqlCommand cmd = new SqlCommand(sql, coon);
        SqlDataReader sda = cmd.ExecuteReader();
        ORANGE_PROJECTModel.Customer c = new ORANGE_PROJECTModel.Customer();
        while (sda.Read())
        {
            c.CustomerID = sda.GetString(0);
            c.CustomerName = sda.GetString(1);
            c.Phone = sda.GetString(2);
            c.Email = sda.GetString(3);
            c.Address = sda.GetString(4);
            c.Password = sda.GetString(5);
        }
        coon.Close();
        return c;
    }

    public bool AddCustomer(string CustomerID, string CustomerName, string Phone, string Email, string Address)
    {
        if (coon.State == ConnectionState.Closed)
            coon.Open();
        string queryAddCus = "INSERT INTO Customer VALUES(@CustomerID,@CustomerName,@Phone,@Email,@Address,default)";
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = coon;

        cmd.CommandText = queryAddCus;
        cmd.Parameters.AddWithValue("@CustomerID", CustomerID);
        cmd.Parameters.AddWithValue("@CustomerName", CustomerName);
        cmd.Parameters.AddWithValue("@Phone", Phone);
        cmd.Parameters.AddWithValue("@Email", Email);
        cmd.Parameters.AddWithValue("@Address", Address);
        int result = cmd.ExecuteNonQuery();
        coon.Close();
        return result > 0 ? true : false;
    }

    public string MakeCustomerID(string FistCharServiceCategory, int ServiceCategoryID)
    {
        if (coon.State == ConnectionState.Closed)
            coon.Open();
        string queryCountCus = "SELECT COUNT(*)+1 FROM Contract WHERE Contract.ServiceCategoryID=" + ServiceCategoryID;
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = coon;
        cmd.CommandText = queryCountCus;
        SqlDataReader dr = cmd.ExecuteReader();
        dr.Read();
        string CustomerID = FistCharServiceCategory + dr.GetInt32(0).ToString("D5");
        dr.Close();
        return CustomerID;
    }

    public bool SendFeedBack(string p1, string p2, string p3)
    {
        if (coon.State == ConnectionState.Closed)
            coon.Open();
        string query = "INSERT INTO Feedback VALUES(@Name,@Email,@Message)";
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = coon;

        cmd.CommandText = query;
        cmd.Parameters.AddWithValue("@Name", p1);
        cmd.Parameters.AddWithValue("@Email", p2);
        cmd.Parameters.AddWithValue("@Message", p3);
        int result = cmd.ExecuteNonQuery();
        coon.Close();
        return result > 0 ? true : false;
    }
}